Imports Microsoft.VisualBasic
Imports System.Reflection
Imports System.Text

Public Class Entity
    Public Class ConnHelperNotGivenException
        Inherits System.ApplicationException
    End Class

    Private _PrimaryKey As String
    Public ReadOnly Property PrimaryKey() As String
        Get
            If String.IsNullOrEmpty(_PrimaryKey) Then
                Dim c As EntityAttribute()
                c = Me.GetType.GetCustomAttributes(GetType(EntityAttribute), False)
                If c.Length = 0 OrElse String.IsNullOrEmpty(c(0).PrimaryKeyName) Then
                    Return "id"
                End If
                _PrimaryKey = c(0).PrimaryKeyName
            End If
            Return _PrimaryKey
        End Get
    End Property

    Public id As Field.Int
    Public Loaded As Boolean
    'Public Entity As Entity

    Public Shared DefaultConnHelper As Katze.IConnHelper
    Private _ConnHelper As Katze.IConnHelper
    Public Property ConnHelper() As Katze.IConnHelper
        Get
            If _ConnHelper Is Nothing Then
                If DefaultConnHelper Is Nothing Then
                    Throw New ConnHelperNotGivenException
                End If
                Return DefaultConnHelper
            Else
                Return _ConnHelper
            End If
        End Get
        Set(ByVal value As Katze.IConnHelper)
            _ConnHelper = value
        End Set
    End Property

    Public Function show() As String
        Dim sb As New StringBuilder
        sb.Append(Me.GetType.FullName)

        For Each i As FieldInfo In Me.GetType.GetFields
            sb.Append(i.Name)
            sb.Append(":")
            If i.FieldType.BaseType Is GetType(Field) Then
                sb.Append(i.GetValue(Me).value)
            End If

            sb.Append("<br>")
        Next
        Return sb.ToString
    End Function

    Public Sub Load()
        'If (Not Me.Entity Is Nothing) AndAlso Me.Entity.Loaded = False Then
        '    Me.Entity.Load()
        'End If
        If Me.id.Loaded = False Then
            Throw New Exception("Primary Key not given")
        End If
        LoadById(Me.id.value)
    End Sub

    Default Public Property DataItem(ByVal key As String) As Object
        Get
            Dim ColumnName As String
            Dim subColumnName As String = ""

            Dim pos As Integer
            pos = key.IndexOf(".")
            If pos > 0 Then
                ColumnName = key.Substring(0, pos)
                subColumnName = key.Substring(pos + 1)
            Else
                ColumnName = key
            End If

            For Each i As FieldInfo In Me.GetType.GetFields
                If i.Name = ColumnName Then
                    If i.FieldType.BaseType Is GetType(Field) Then
                        Return i.GetValue(Me).value
                    ElseIf i.FieldType.BaseType Is GetType(Entity) Then
                        If String.IsNullOrEmpty(subColumnName) Then
                            Return i.GetValue(Me).id.value
                        Else
                            Return i.GetValue(Me).DataItem(subColumnName)
                        End If
                    End If
                End If
            Next

            Return Nothing
        End Get
        Set(ByVal value As Object)
            Me.id.value = value
        End Set
    End Property

    Public Sub FindById(ByVal id As Integer)
        Me.LoadById(id)
    End Sub

    Public Function Count(Optional ByVal whereSql As String = Nothing) As Integer
        Dim sql As String

        If String.IsNullOrEmpty(whereSql) Then
            sql = "select count(*) from [" & GetTableName(Me) & "]"
        Else
            sql = "select count(*) from [" & GetTableName(Me) & "] where " & whereSql
        End If

        Return Me.ConnHelper.exeInt(sql)
    End Function

    Public Function Find(ByVal whereSql As String, Optional ByVal orderSql As String = "", Optional ByVal Start As Integer = -1, Optional ByVal Count As Integer = 0) As Collection
        Dim c As New Collection
        Dim dt As DataTable
        Dim sql As String

        If String.IsNullOrEmpty(whereSql) Then
            sql = "select * from [" & GetTableName(Me) & "]"
        Else
            sql = "select * from [" & GetTableName(Me) & "] where " & whereSql
        End If

        If Start > -1 Then
            If String.IsNullOrEmpty(orderSql) Then
                Throw New System.ArgumentException("Must Provide orderSql when start is given")
            End If
            sql = Me.ConnHelper.GetPagingSQL(sql, orderSql, Start, Count)
        Else
            If Not String.IsNullOrEmpty(orderSql) Then
                sql &= " order by " & orderSql
            End If
        End If

        dt = Me.ConnHelper.exeTable(sql)
        For Each dr As DataRow In dt.Rows
            Dim o As Object
            o = Me.GetType.GetConstructor(Type.EmptyTypes).Invoke(Nothing)
            o.Map(dr)
            o.id.value = dr.Item(Me.PrimaryKey)
            c.Add(o)
        Next
        Return c
    End Function

    Public Function ReverseFind(ByVal EntityType As Type, Optional ByVal orderSql As String = "", Optional ByVal Start As Integer = -1, Optional ByVal Count As Integer = 0) As Collection
        Dim MyType As Type = Me.GetType
        Dim ColumnName As String
        For Each i As FieldInfo In EntityType.GetFields()
            If i.FieldType Is MyType Then
                ColumnName = GetColumnName(i)
                Dim c As ConstructorInfo
                c = EntityType.GetConstructor(Type.EmptyTypes)
                Return c.Invoke(Nothing).Find("[" & ColumnName & "]=" & Me.id.value, orderSql, Start, Count)
            End If
        Next

        Throw New System.ArgumentException("Reserse Field finding Error")
    End Function

    Private Function GetColumnName(ByVal t As FieldInfo) As String
        'todo, should cache column schema
        Dim c As FieldAttribute()
        c = t.GetCustomAttributes(GetType(FieldAttribute), False)
        If c.Length = 0 OrElse String.IsNullOrEmpty(c(0).ColumnName) Then
            Return t.Name
        End If
        Return c(0).ColumnName
    End Function

    Public Sub Map(ByVal dr As DataRow)
        Dim ic As Field.Int
        Dim ColumnName As String

        For Each i As FieldInfo In Me.GetType.GetFields
            If i.FieldType.BaseType Is GetType(Field) Then
                ColumnName = GetColumnName(i)
                If i.FieldType Is GetType(Field.Int) Then
                    If ColumnName <> Me.PrimaryKey Then
                        If Not dr.IsNull(ColumnName) Then
                            ic = i.GetValue(Me)
                            ic.value = dr.Item(ColumnName)
                            i.GetValue(Me).Updated = False
                        Else
                            i.GetValue(Me).Loaded = True
                        End If
                    End If
                Else
                    If Not dr.IsNull(ColumnName) Then
                        i.GetValue(Me).value = dr.Item(ColumnName)
                        i.GetValue(Me).Updated = False
                    Else
                        i.GetValue(Me).Loaded = True
                    End If
                End If
            ElseIf i.FieldType.BaseType Is GetType(Entity) Then
                ColumnName = GetColumnName(i)
                If Not dr.IsNull(ColumnName) Then
                    i.GetValue(Me).id.value = dr.Item(ColumnName)
                    i.GetValue(Me).Updated = False
                End If
            End If
        Next
        Me.Loaded = True
    End Sub

    Private Sub LoadById(ByVal id As Integer)
        'If (Not Me.Entity Is Nothing) AndAlso Me.Entity.Loaded = False Then
        '    Me.Entity.Load()
        'End If

        Dim dr As DataRow
        dr = Me.ConnHelper.exeDataRow("select * from [" & GetTableName(Me) & "] where [" & Me.PrimaryKey & "]=" & id)
        If dr Is Nothing Then
            Throw New HelperExceptions.NullDataExcetion
        End If
        Map(dr)
        Me.id.value = id
    End Sub

    Public Sub Delete()
        Me.ConnHelper.exeNonQuery("delete from [" & GetTableName(Me) & "] where [" & Me.PrimaryKey & "]=" & Me.id.value)
    End Sub

    Private Shared Function GetColumnType(ByVal atts As FieldInfo) As System.Data.SqlDbType
        Dim c As FieldAttribute
        c = atts.GetCustomAttributes(GetType(FieldAttribute), False)(0)
        Return c.ColumnType
    End Function

    Private Shared Function GetSize(ByVal atts As FieldInfo) As Integer
        Dim c As FieldAttribute
        c = atts.GetCustomAttributes(GetType(FieldAttribute), False)(0)
        Return c.Size
    End Function

    Private Shared Function GetTableName(ByVal t As Object) As String
        Dim c As EntityAttribute()
        c = t.GetType.GetCustomAttributes(GetType(EntityAttribute), False)
        If c.Length = 0 OrElse String.IsNullOrEmpty(c(0).TableName) Then
            Return t.GetType.Name
        End If
        Return c(0).TableName
    End Function

    Private Sub Update()
        Dim pa As IParaHelper
        Dim ColumnName As String
        pa = Me.ConnHelper.GetParaHelper
        Dim pk As String
        pk = Me.PrimaryKey()
        pa.addInt(pk, Me.id.value)

        For Each i As FieldInfo In Me.GetType.GetFields
            If i.FieldType.BaseType Is GetType(Entity) Then
                ColumnName = Me.GetColumnName(i)
                If ColumnName <> pk Then
                    pa.addInt(ColumnName, i.GetValue(Me).id.value())
                End If
            ElseIf i.FieldType.BaseType Is GetType(Field) Then
                ColumnName = Me.GetColumnName(i)
                If i.GetValue(Me).Updated And pk <> ColumnName Then
                     AssignParam(ColumnName, pa, i)
                End If
            End If
        Next
        Dim sql As String
        sql = pa.GetUpdateSQL(GetTableName(Me), Me.PrimaryKey)

        Me.ConnHelper.exeNonQuery(sql, pa.Parameters)
    End Sub

    Public Sub Save()
        If Me.id.Loaded = False Then
            Me.AddNew()
        Else
            Me.Update()
        End If
    End Sub

    Private Sub AssignParam(ByVal ColumnName As String, ByVal pa As IParaHelper, ByVal i As FieldInfo)
        'todo, should handle all possbile column type
        If i.FieldType Is GetType(Field.Int) Then
            pa.addInt(ColumnName, i.GetValue(Me).value())
        ElseIf i.FieldType Is GetType(Field.Text) Then
            Select Case Entity.GetColumnType(i)
                Case SqlDbType.VarChar
                    pa.addVarchar(ColumnName, i.GetValue(Me).value(), Entity.GetSize(i))
                Case SqlDbType.NChar
                    pa.addNChar(ColumnName, i.GetValue(Me).value(), Entity.GetSize(i))
                Case SqlDbType.NVarChar
                    pa.addNVarchar(ColumnName, i.GetValue(Me).value(), Entity.GetSize(i))
                Case SqlDbType.Char
                    pa.addChar(ColumnName, i.GetValue(Me).value(), Entity.GetSize(i))
            End Select
        ElseIf i.FieldType Is GetType(Field.SmallDateTime) Then
            pa.addSmallDateTime(ColumnName, i.GetValue(Me).value())
        ElseIf i.FieldType Is GetType(Field.Bit) Then
            pa.addBit(ColumnName, i.GetValue(Me).value())
        ElseIf i.FieldType Is GetType(Field.Binary) Then
            pa.addBinary(ColumnName, i.GetValue(Me).value())
        ElseIf i.FieldType Is GetType(Field.DateTime) Then
            pa.addDateTime(ColumnName, i.GetValue(Me).value())
        ElseIf i.FieldType Is GetType(Field.TinyInt) Then
            pa.addTinyInt(ColumnName, i.GetValue(Me).value())
        End If
    End Sub

    Private Sub AddNew()
        Dim pa As IParaHelper
        pa = Me.ConnHelper.GetParaHelper
        Dim c As Field
        Dim ColumnName As String
        Dim pk As String = Me.PrimaryKey()

        For Each i As FieldInfo In Me.GetType.GetFields
            If i.FieldType.BaseType Is GetType(Field) Then
                ColumnName = Me.GetColumnName(i)
                If ColumnName <> pk Then
                    c = i.GetValue(Me)
                    If c.Updated Then
                        AssignParam(ColumnName, pa, i)
                    End If
                End If
            End If
        Next

        Dim sql As String
        Dim TableName As String
        TableName = GetTableName(Me)
        sql = pa.GetInsertSQL(TableName)

        Me.ConnHelper.exeNonQuery(sql, pa.Parameters)
        Me.id.value = Me.ConnHelper.exeInt("select max(" & pk & ") from [" & TableName & "]")
    End Sub

    Public Sub New()
        init()
    End Sub

    ''' <summary>
    ''' Helper Method to Instanized all members inherited from Entity or Field
    ''' </summary>
    ''' <remarks></remarks>
    Private Sub init()
        Dim c As ConstructorInfo

        For Each i As FieldInfo In Me.GetType.GetFields
            If i.FieldType.BaseType Is GetType(Field) Or i.FieldType.BaseType Is GetType(Entity) Then
                c = i.FieldType.GetConstructor(Type.EmptyTypes)
                i.SetValue(Me, c.Invoke(Nothing))
                i.GetValue(Me).Entity = Me
            End If
        Next
    End Sub
End Class

<AttributeUsage(AttributeTargets.All, AllowMultiple:=False, Inherited:=False)> _
Public Class EntityAttribute
    Inherits Attribute

    Public PrimaryKeyName As String = "id"
    Public TableName As String
End Class